﻿/*  Copyright 2012 PerceiveIT Limited
 *  This file is part of the Scryber library.
 *
 *  You can redistribute Scryber and/or modify 
 *  it under the terms of the GNU Lesser General Public License as published by
 *  the Free Software Foundation, either version 3 of the License, or
 *  (at your option) any later version.
 * 
 *  Scryber is distributed in the hope that it will be useful,
 *  but WITHOUT ANY WARRANTY; without even the implied warranty of
 *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *  GNU Lesser General Public License for more details.
 * 
 *  You should have received a copy of the GNU Lesser General Public License
 *  along with Scryber source code in the COPYING.txt file.  If not, see <http://www.gnu.org/licenses/>.
 * 
 */

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml.XPath;
using System.Data;
using System.Data.Common;

namespace Scryber.Data
{
    /// <summary>
    /// Encapsulates a SQL command to a database.
    /// </summary>
    [PDFRequiredFramework("0.9.0")]
    [PDFParsableComponent("SqlCommand")]
    public class PDFSqlProviderCommand : PDFXPathProviderCommandBase
    {
        //the category name of the sql command
        internal const string SqlCommandLog = "Sql Command"; 
        
        #region public string Statement {get;set;}

        private string _statement;
        /// <summary>
        /// Gets or sets the SQL statement to be executed
        /// </summary>
        [PDFAttribute("statement")]
        [PDFElement("Statement")]
        public string Statement
        {
            get { return _statement; }
            set
            {
                _statement = value;
            }
        }

        #endregion

        #region public CommandType CommandType {get;set;}

        private CommandType _type = CommandType.Text;

        /// <summary>
        /// Gets or sets the type of SQL command to be executed
        /// </summary>
        [PDFAttribute("command-type")]
        public CommandType CommandType
        {
            get { return _type; }
            set { _type = value; }
        }

        #endregion

        #region public string ConnectionName {get;set;}

        private string _conname;

        /// <summary>
        /// Gets or sets the name of the connection string to use in the config file.
        /// </summary>
        [PDFAttribute("connection-name")]
        public string ConnectionName
        {
            get { return _conname; }
            set { _conname = value; }
        }

        #endregion

        #region public string ConnectionString {get;set;}

        private string _constring;

        /// <summary>
        /// Gets or sets the explict connection string to a sql data source. 
        /// This will be used as a higher priority than the connection name
        /// </summary>
        [PDFAttribute("connection-string")]
        public string ConnectionString
        {
            get { return _constring; }
            set { _constring = value; }
        }

        #endregion

        #region public string ConnectionProvider {get;set;}

        private string _prov = "System.Data.SqlClient";

        /// <summary>
        /// Gets or sets the provider name for an explicit connection 
        /// to a sql data source. It is a required value if the connection string is set, but defaulted to SQLClient provider
        /// </summary>
        [PDFAttribute("connection-provider")]
        public string ConnectionProvider
        {
            get { return _prov; }
            set { _prov = value; }
        }

        #endregion

        #region public string ElementName {get;set;}

        private string _elename;

        /// <summary>
        /// Gets or sets the element name. The name that will be used when generating the XML for each item returned from this command
        /// </summary>
        [PDFAttribute("element-name")]
        public string ElementName
        {
            get { return _elename; }
            set { _elename = value; }
        }

        #endregion

        #region public string ElementNamespaceUri { get; set; }

        /// <summary>
        /// Gets or sets the namespace uri of the root element
        /// </summary>
        [PDFAttribute("element-namespace")]
        public string ElementNamespaceUri { get; set; }

        #endregion

        #region public string AsAttributes
        /// <summary>
        /// Gets or sets the comma separated list names of the returned data fields that should be encoded as 
        /// attributes rather than inner elements. Default is none (blank), 
        /// the special value * will make all fileds be output as attributes.
        /// </summary>
        [PDFAttribute("attributes")]
        public string AsAttributes
        {
            get;
            set;
        }

        #endregion

        #region public PDFSqlParameterList Parameters {get;} + HasParameters {get;}

        private PDFSqlParameterList _params;

        /// <summary>
        /// Gets the list of parameters in this command
        /// </summary>
        [PDFElement("Parameters")]
        [PDFArray(typeof(PDFSqlParameter))]
        public PDFSqlParameterList Parameters
        {
            get
            {
                if (null == _params)
                    _params = new PDFSqlParameterList();
                return _params;
            }
        }

        public bool HasParameters
        {
            get { return null != _params && _params.Count > 0; }
        }

        #endregion

        #region public PDFSqlRelationList Relations {get;} + HasRelations{get;}

        private PDFSqlRelationList _rels;

        /// <summary>
        /// Gets the relations in this command
        /// </summary>
        [PDFArray(typeof(PDFSqlRelation))]
        [PDFElement("Relations")]
        public PDFSqlRelationList Relations
        {
            get
            {
                if (null == _rels)
                    _rels = new PDFSqlRelationList();
                return _rels;
            }
        }

        /// <summary>
        /// Returns true if this command has related commands
        /// </summary>
        public bool HasRelations
        {
            get { return null != _rels && _rels.Count > 0; }
        }

        #endregion

        //
        // temp data storage methods
        //

        #region public PDFDataSchema DataSchema {get; private set;}

        /// <summary>
        /// Gets the Data Schema associated with the last loaded data.
        /// </summary>
        public PDFDataSchema DataSchema
        {
            get;
            private set;
        }

        #endregion

        #region public DataSet DataSet {get; private set;}

        /// <summary>
        /// Gets the DataSet from the last loaded data
        /// </summary>
        public DataSet DataSet
        {
            get;
            private set;
        }

        #endregion

        //
        // ctor
        //

        #region public PDFSqlCommand()

        public PDFSqlProviderCommand()
            : this(PDFObjectTypes.SqlCommandType)
        {
        }

        #endregion

        #region protected PDFSqlCommand(PDFObjectType type)

        protected PDFSqlProviderCommand(PDFObjectType type)
            : base(type)
        {
        }

        #endregion

        //
        // override methods
        //

        #region public override object GetNullValue()

        /// <summary>
        /// Gets the native null or empty value for the commands source
        /// </summary>
        /// <returns></returns>
        public object GetNullValue(System.Data.DbType type)
        {
            return System.DBNull.Value;
        }

        #endregion

        #region protected override System.Xml.XPath.XPathNavigator DoLoadXPathData(PDFXPathDataSourceBase source, PDFDataContext context)

        /// <summary>
        /// Main method that loads all the data (including any related commands) into a dataset and returns an XPathNavigable instance of the data representation
        /// </summary>
        /// <param name="source"></param>
        /// <param name="context"></param>
        /// <returns></returns>
        protected override System.Xml.XPath.XPathNavigator DoLoadXPathData(PDFXPathDataSourceBase source, PDFDataContext context)
        {
            DataSet ds = new DataSet(source.ID);

            //We are always top level here so top element namespace should be the same as inner element namespace
            if (!string.IsNullOrEmpty(this.ElementNamespaceUri))
                ds.Namespace = this.ElementNamespaceUri;
            string tblname = this.GetDataTableName(ds);
            int index = 0;
            this.FillData(ds, source, null, context);
            
            index++;

            if(this.HasRelations)
            {
                foreach (PDFSqlRelation rel in this.Relations)
                {
                    FillRelatedData(rel, ds, source, context);
                }
            }

            this.DataSet = ds;
            this.DataSchema = DoPopulateDataSchema(ds, context);

            XPathNavigator nav = this.ConvertDataSetToXPath(ds, context);
            return nav;
        }

        #endregion

        #region protected override void DoDataBind(PDFDataContext context)

        /// <summary>
        /// Overrides the base implementation to ensure that the parameters and relations are databound
        /// </summary>
        /// <param name="context"></param>
        protected override void DoDataBind(PDFDataContext context)
        {
            base.DoDataBind(context);
            if (this.HasParameters)
                this.Parameters.DataBind(context);
            if (this.HasRelations)
                this.Relations.DataBind(context);
        }

        #endregion

        //
        // support methods
        //

        #region protected virtual void ApplyAttributes(DataSet ds, string attributenames, PDFDataContext context)

        /// <summary>
        /// Sets the column mapping for this commands data table columns to attributes
        /// based on the comma separated list of names (or the catch all *)
        /// </summary>
        /// <param name="ds"></param>
        /// <param name="attributenames"></param>
        /// <param name="context"></param>
        protected virtual void ApplyAttributes(DataSet ds, string attributenames, PDFDataContext context)
        {
            DataTable dt = ds.Tables[this.GetDataTableName(ds)];
            if (attributenames == "*")
            {
                foreach (DataColumn dc in dt.Columns)
                {
                    dc.ColumnMapping = MappingType.Attribute;
                }
            }
            else
            {
                string[] all = attributenames.Split(',');
                foreach (string name in all)
                {
                    DataColumn dc;
                    if (dt.Columns.Contains(name.Trim()))
                    {
                        dc = dt.Columns[name.Trim()];
                        dc.ColumnMapping = MappingType.Attribute;
                    }
                    else
                        context.TraceLog.Add(TraceLevel.Warning, SqlCommandLog, "The data column '" + name + "could not be found, so cannot be set as a mapping type of Attribute");
                }
            }
        }

        #endregion

        #region protected virtual XPathNavigator ConvertDataSetToXPath(DataSet ds, PDFDataContext context)

        /// <summary>
        /// Converts the provided dataset into an XPathNavigator instance.
        /// </summary>
        /// <param name="ds"></param>
        /// <param name="context"></param>
        /// <returns></returns>
        protected virtual XPathNavigator ConvertDataSetToXPath(DataSet ds, PDFDataContext context)
        {
            using (System.IO.MemoryStream ms = new System.IO.MemoryStream())
            {
                ds.WriteXml(ms);
                ms.Flush();
                ms.Position = 0;
                XPathDocument doc = new XPathDocument(ms);
                return doc.CreateNavigator();
            }
        }

        #endregion

        #region protected virtual void FillRelatedData(PDFSqlRelation rel, DataSet ds, PDFXPathDataSourceBase source, PDFDataContext context)

        /// <summary>
        /// Fills the data into the dataset from the releated command, and then adds the relation constraint to the data set.
        /// </summary>
        /// <param name="rel"></param>
        /// <param name="ds"></param>
        /// <param name="source"></param>
        /// <param name="context"></param>
        protected virtual void FillRelatedData(PDFSqlRelation rel, DataSet ds, PDFXPathDataSourceBase source, PDFDataContext context)
        {
            string cmd = rel.ChildCommand;
            PDFXPathProviderCommandBase provider = source.Commands[cmd];
            if (null == provider)
                throw new NullReferenceException(string.Format(Errors.CommandWithNameCannotBeFound, cmd, source.ID));
            if (!(provider is PDFSqlProviderCommand))
                throw new InvalidCastException(string.Format(Errors.CommandForRelatedDataMustMatchType, typeof(PDFSqlProviderCommand), typeof(PDFSqlProviderCommand)));
            
            PDFSqlProviderCommand sqlProvider = (PDFSqlProviderCommand)provider;
            sqlProvider.FillData(ds, source, this, context);

            rel.AddRelation(this, sqlProvider, ds, context);
            
        }

        #endregion

        #region public void FillData(DataSet ds, PDFProviderParameterValueCollection values)

        /// <summary>
        /// Loads all the required data from this commands Sql source into the dataset using the
        /// Sql statement and the provided parameter values that have ben explicitly set
        /// </summary>
        /// <param name="ds">the DataSet to populate</param>
        /// <param name="index">The index of the command</param>
        public void FillData(DataSet ds, PDFXPathDataSourceBase source, PDFSqlProviderCommand parent, PDFDataContext context)
        {
            bool logDebug = context.TraceLog.ShouldLog(TraceLevel.Debug);

            if (logDebug)
                context.TraceLog.Begin(TraceLevel.Debug, SqlCommandLog, string.Format("Starting load of data for sqlCommand '{0}'", this.CommandID));

            DbConnection con = null;
            DbCommand cmd = null;

            try
            {
                con = this.CreateConnection(source, parent);
                if (logDebug)
                    context.TraceLog.Add(TraceLevel.Debug, SqlCommandLog, "Created db connection to sql database");
                
                cmd = this.CreateCommand(con, this.CommandType, this.Statement);
                if (logDebug)
                    context.TraceLog.Add(TraceLevel.Debug, SqlCommandLog, "Created db command for statement");
                
                this.PopulateParameters(cmd, context);
                
                if (logDebug)
                    context.TraceLog.Add(TraceLevel.Debug, SqlCommandLog, "Added " + cmd.Parameters.Count + " parameters to db command");
                
                con.Open();
                this.PopulateData(ds, cmd);

                if (!string.IsNullOrEmpty(this.AsAttributes))
                    ApplyAttributes(ds, this.AsAttributes, context);

                if (logDebug)
                    context.TraceLog.Add(TraceLevel.Debug, SqlCommandLog, "Dataset populated");
                
            }
            finally
            {
                if (null != cmd)
                    cmd.Dispose();
                if (null != con)
                    con.Dispose();

                if (logDebug)
                    context.TraceLog.Add(TraceLevel.Debug, SqlCommandLog, string.Format("Disposed of command and connection"));

            }

            if (logDebug)
                context.TraceLog.End(TraceLevel.Debug, SqlCommandLog, string.Format("Completed load of data for sqlCommand '{0}'", this.CommandID));

        }

        #endregion

        #region protected virtual string GetDataTableName()

        /// <summary>
        /// returns the name that sould be given to a data table that has been loaded.
        /// </summary>
        /// <param name="index">The index of the table being loaded. The first table will be 1.</param>
        /// <returns></returns>
        public virtual string GetDataTableName(DataSet ds)
        {
            if (!string.IsNullOrEmpty(this.ElementName))
                return this.ElementName;
            if (!string.IsNullOrEmpty(this.CommandID))
                return this.CommandID;
            else
            {
                this.CommandID = "Command" + ds.Tables.Count + 1;
                return this.CommandID;
            }
        }

        #endregion

        //
        // data schema methods
        //

        #region protected virtual PDFDataSchema DoPopulateDataSchema(DataSet dataset, PDFDataContext context)

        protected virtual PDFDataSchema DoPopulateDataSchema(DataSet dataset, PDFDataContext context)
        {
            string dsName = dataset.DataSetName;
            string dsXmlName = System.Xml.XmlConvert.EncodeLocalName(dsName);
            System.Data.DataTable[] tables = GetTopLevelTables(dataset);
            List<PDFDataItem> topItems = new List<PDFDataItem>();

            foreach (System.Data.DataTable table in tables)
            {
                string tableName = table.TableName;
                string tableXmlName = System.Xml.XmlConvert.EncodeLocalName(tableName);
                string path = PDFDataSchema.CombineElementNames(dsXmlName, tableXmlName);
                PDFDataItemCollection columns = new PDFDataItemCollection(GetTableColumnSchema(path, table));
                PDFDataItem topTable = new PDFDataItem(path, tableName, tableName, columns);
                topItems.Add(topTable);

                //TODO: Load child schemas
            }
            PDFDataSchema schema = new PDFDataSchema(dsName, new PDFDataItemCollection(topItems));
            return schema;
        }

        #endregion

        #region private IEnumerable<PDFDataItem> GetTableColumnSchema(string path, System.Data.DataTable table)

        /// <summary>
        /// Extracts a bunch of data items for the schema from the table columns
        /// </summary>
        /// <param name="path">the current path to the table</param>
        /// <param name="table">The data table to extract the columns for</param>
        /// <returns></returns>
        private IEnumerable<PDFDataItem> GetTableColumnSchema(string path, System.Data.DataTable table)
        {
            List<PDFDataItem> all = new List<PDFDataItem>();
            foreach (System.Data.DataColumn col in table.Columns)
            {
                if (col.ColumnMapping == System.Data.MappingType.Hidden)
                    ;
                else
                {
                    PDFDataItem item = GetColumnDataItem(path, col);
                    if (null != item)
                        all.Add(item);
                }
            }
            return all;
        }

        #endregion

        #region private PDFDataItem GetColumnDataItem(string path, System.Data.DataColumn col)

        /// <summary>
        /// Extracts a single schema data item for the provided data column
        /// </summary>
        /// <param name="path"></param>
        /// <param name="col"></param>
        /// <returns></returns>
        private PDFDataItem GetColumnDataItem(string path, System.Data.DataColumn col)
        {
            string name = col.ColumnName;
            string relativePath = System.Xml.XmlConvert.EncodeLocalName(name);
            string title = string.IsNullOrEmpty(col.Caption) ? name : col.Caption;

            System.Xml.XmlNodeType nodetype;
            if (col.ColumnMapping == System.Data.MappingType.Hidden)
                return null;
            else if (col.ColumnMapping == System.Data.MappingType.Attribute)
            {
                relativePath = "@" + relativePath;
                nodetype = System.Xml.XmlNodeType.Attribute;
            }
            else if (col.ColumnMapping == System.Data.MappingType.SimpleContent)
            {
                relativePath = "text()";
                nodetype = System.Xml.XmlNodeType.Text;
            }
            else if (col.ColumnMapping == System.Data.MappingType.Element)
            {
                nodetype = System.Xml.XmlNodeType.Element;
                relativePath += "/text()";
            }
            else
                throw new ArgumentOutOfRangeException("col.ColumnMapping");

            string fullpath = PDFDataSchema.CombineElementNames(path, relativePath);

            PDFDataItem item = new PDFDataItem(fullpath, relativePath, name, title, nodetype, col.DataType);
            return item;

        }

        #endregion

        #region private System.Data.DataTable[] GetTopLevelTables(System.Data.DataSet dataset)

        /// <summary>
        /// Gets all the tables in the dataset that do not have a parent relation (i.e. they are at the top of the set).
        /// </summary>
        /// <param name="dataset"></param>
        /// <returns></returns>
        private System.Data.DataTable[] GetTopLevelTables(System.Data.DataSet dataset)
        {
            List<System.Data.DataTable> found = new List<System.Data.DataTable>();
            foreach (System.Data.DataTable dt in dataset.Tables)
            {
                if (dt.ParentRelations == null || dt.ParentRelations.Count == 0)
                    found.Add(dt);
            }
            return found.ToArray();
        }

        #endregion
        
        //
        // SQL DB Command Methods
        //

        #region private void PopulateData(DataSet ds, int index, DbCommand cmd)

        /// <summary>
        /// Fills a the dataset with the data as a new datatable after executing the command
        /// </summary>
        /// <param name="cmd"></param>
        /// <returns></returns>
        private void PopulateData(DataSet ds, DbCommand cmd)
        {
            using (DbDataReader reader = cmd.ExecuteReader())
            {
                string tname = GetDataTableName(ds);
                DataTable dt = new DataTable(tname);

                if (!string.IsNullOrEmpty(this.ElementNamespaceUri))
                    dt.Namespace = this.ElementNamespaceUri;

                dt.Load(reader, LoadOption.OverwriteChanges);
                ds.Tables.Add(dt);
                dt.AcceptChanges();
            }
        }

        #endregion

        #region private void PopulateParameters(DbCommand cmd, PDFProviderParameterValueCollection values)

        /// <summary>
        /// Adds the parameters defined on this PDFSqlCommand to the DbCommand setting their properties and value
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="values"></param>
        private void PopulateParameters(DbCommand cmd, PDFDataContext context)
        {
            if (this.HasParameters)
            {
                foreach (PDFSqlParameter param in this.Parameters)
                {
                    DbParameter dbparm = cmd.CreateParameter();
                    dbparm.DbType = param.DataType;
                    if (param.Size > 0)
                        dbparm.Size = param.Size;
                    dbparm.ParameterName = param.ParameterName;
                    dbparm.Value = param.GetParameterValue(this, context);
                    cmd.Parameters.Add(dbparm);
                }
            }
        }

        #endregion

        #region private DbCommand CreateCommand(DbConnection con, System.Data.CommandType commandType, string statement)

        /// <summary>
        /// Creates a new DbCommand with the connection, type and Sql statement.
        /// </summary>
        /// <param name="con"></param>
        /// <param name="commandType"></param>
        /// <param name="statement"></param>
        /// <returns></returns>
        private DbCommand CreateCommand(DbConnection con, System.Data.CommandType commandType, string statement)
        {
            DbCommand cmd = con.CreateCommand();
            cmd.Connection = con;
            cmd.CommandType = commandType;
            cmd.CommandText = statement;
            return cmd;
        }

        #endregion

        #region private DbConnection CreateConnection(PDFXPathDataSourceBase source, PDFSqlProviderCommand parent)

        /// <summary>
        /// Creates a new connection to the Sql database. Returns connection closed.
        /// </summary>
        /// <returns></returns>
        private DbConnection CreateConnection(PDFXPathDataSourceBase source, PDFSqlProviderCommand parent)
        {
            if (null != parent)
            {
                if (string.IsNullOrEmpty(this.ConnectionName))
                    this.ConnectionName = parent.ConnectionName;

                if (string.IsNullOrEmpty(this.ConnectionString))
                {
                    this.ConnectionString = parent.ConnectionString;
                    this.ConnectionProvider = parent.ConnectionProvider;
                }
            }

            string constr;
            string provider;

            if (!string.IsNullOrEmpty(this.ConnectionString))
            {
                constr = this.ConnectionString;
                provider = this.ConnectionProvider;
            }
            else if (!string.IsNullOrEmpty(this.ConnectionName))
            {
                System.Configuration.ConnectionStringSettings consettings = System.Configuration.ConfigurationManager.ConnectionStrings[this.ConnectionName];
                if (null == consettings)
                    throw new NullReferenceException(string.Format(Errors.NoConnectionSettingFor, this.ConnectionName));
                constr = consettings.ConnectionString;
                provider = consettings.ProviderName;
            }
            else
                throw new NullReferenceException(string.Format(Errors.NoConnectionDefined, this.CommandID, source.ID));

            DbProviderFactory fact = DbProviderFactories.GetFactory(provider);
            if (null == fact)
                throw new NullReferenceException(string.Format(Errors.NoProviderFactoryWithName, provider));
            
            DbConnection con = fact.CreateConnection();
            con.ConnectionString = constr;
            return con;
        }

        #endregion

    }
}
